CREATE TABLE `role` (
  `RoleID` decimal(10,0) NOT NULL,
  `RoleDescription` varchar(45) default NULL,
  PRIMARY KEY  (`RoleID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `user` (
  `UserID` decimal(10,0) NOT NULL,
  `JameInsanID` varchar(20) default NULL,
  `InsanID` varchar(20) default NULL,
  `Name` varchar(20) default NULL,
  `Adress` varchar(45) default NULL,
  `Block` varchar(45) default NULL,
  `District` varchar(45) default NULL,
  `State` varchar(20) default NULL,
  `Country` varchar(20) default NULL,
  `ContactNumber` decimal(10,0) default NULL,
  `RoleID` decimal(10,0) default NULL,
  `Password` varchar(45) default NULL,
  `Status` varchar(20) default NULL,
  PRIMARY KEY  (`UserID`),
  KEY `Role ID` (`RoleID`),
  CONSTRAINT `Role ID` FOREIGN KEY (`RoleID`) REFERENCES `role` (`RoleID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `user_role` (
  `UserID` decimal(10,0) default NULL,
  `RoleID` decimal(10,0) default NULL,
  `StartDate` date default NULL,
  `EndDate` date default NULL,
  `Status` varchar(20) default NULL,
  KEY `User  ID` (`UserID`),
  KEY `Role  ID` (`RoleID`),
  CONSTRAINT `Role  ID` FOREIGN KEY (`RoleID`) REFERENCES `role` (`RoleID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `User  ID` FOREIGN KEY (`UserID`) REFERENCES `user` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `location` (
  `LocationID` decimal(10,0) NOT NULL,
  `LocationCode` varchar(10) default NULL,
  `Name` varchar(45) default NULL,
  `Category` varchar(45) default NULL,
  `Address` varchar(45) default NULL,
  `Block` varchar(45) default NULL,
  `District` varchar(45) default NULL,
  `State` varchar(20) default NULL,
  `Country` varchar(20) default NULL,
  `LandArea` decimal(10,0) default NULL COMMENT 'LandArea: Square Feet',
  `LandTerm` varchar(45) default NULL,
  `LandType` varchar(45) default NULL,
  `RunningFeet` decimal(10,0) default NULL,
  `CoveredArea` decimal(10,0) default NULL,
  `RegisteredOnName` varchar(20) default NULL,
  `RegistrationNo` decimal(10,0) default NULL,
  `RegistrationDate` date default NULL,
  `SubRegistrar` varchar(20) default NULL,
  `EstimatedValue` double default NULL,
  `StampExpenditure` double default NULL,
  `OtherExpenditure` double default NULL,
  `RegistryHolder` varchar(20) default NULL,
  `RegistryCopy` varchar(45) default NULL,
  `BuildingMap` varchar(45) default NULL,
  `Canteen` varchar(5) default NULL,
  `Landmark` varchar(20) default NULL,
  `DistanceFromMainRoad` decimal(10,0) default NULL,
  `ResponsiblePerson` varchar(20) default NULL,
  `Status` varchar(10) default NULL,
  `TotalBalance` double default NULL,
  `GroupId` decimal(10,0) default NULL,
  PRIMARY KEY  (`LocationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `group_master` (
  `id` decimal(11,0) default NULL,
  `name` varchar(100) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `location_group` (
  `GroupId` decimal(10,0) default NULL,
  `LocationId` decimal(10,0) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `location_user` (
  `UserID` decimal(10,0) default NULL,
  `GroupID` decimal(10,0) default NULL,
  `Designation` varchar(45) default NULL,
  `Status` varchar(20) default NULL,
  KEY `Loc  ID` (`GroupID`),
  KEY `Usr_ID` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `video` (
  `VideoID` decimal(10,0) NOT NULL,
  `LocationID` decimal(10,0) default NULL,
  `Path` varchar(45) default NULL,
  `Size` int(11) default NULL,
  PRIMARY KEY  (`VideoID`),
  KEY `Location ID` (`LocationID`),
  CONSTRAINT `Location ID` FOREIGN KEY (`LocationID`) REFERENCES `location` (`LocationID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `image` (
  `ImageID` decimal(10,0) NOT NULL,
  `LocationID` decimal(10,0) default NULL,
  `Path` varchar(45) default NULL,
  `Size` int(11) default NULL,
  PRIMARY KEY  (`ImageID`),
  KEY `Location_ID` (`LocationID`),
  CONSTRAINT `Location_ID` FOREIGN KEY (`LocationID`) REFERENCES `location` (`LocationID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `account` (
  `TransactionID` int(10) NOT NULL auto_increment,
  `FormID` varchar(25) default NULL,
  `LocationID` decimal(10,0) default NULL,
  `Amount` double default NULL,
  `Reason` varchar(45) default NULL,
  `ItemPurchased` varchar(45) default NULL,
  `TransactionDate` date default NULL,
  `ReturnDate` date default NULL,
  `InterestAmount` double default NULL,
  `PersonID` decimal(10,0) default NULL,
  `Narration` varchar(45) default NULL,
  `TransactionType` varchar(10) default NULL,
  `DateTimerStamp` datetime default NULL,
  `ModeOfPayment` varchar(20) default NULL,
  `TransactionAuthorizerID` decimal(10,0) default NULL,
  `Status` varchar(20) default NULL,
  `reject` varchar(20) default 'N',
  PRIMARY KEY  (`TransactionID`),
  KEY `Location  ID` (`LocationID`),
  KEY `TransactionAuthorizerID` (`TransactionAuthorizerID`),
  CONSTRAINT `Location  ID` FOREIGN KEY (`LocationID`) REFERENCES `location` (`LocationID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `TransactionAuthorizerID` FOREIGN KEY (`TransactionAuthorizerID`) REFERENCES `user` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



